package com.gljx.web.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.gljx.web.dao.CommonDao;
import com.gljx.web.dao.ImporExcelMapper;
import com.gljx.web.service.StudentImportService;

@Service
@Transactional
public class StudentImportServiceImpl extends CommonDao implements StudentImportService {
	
	@Autowired
	private ImporExcelMapper ImporExcelMapper;
	
	/**
	 * @param map
	 * 插入主表信息
	 */
	public void addMain(Map<String, String> map ){
		//SqlSession session = this.getSqlSession();
		//session.insert("com.gljx.web.dao.ImporExcelMapper.addMain", map);
		ImporExcelMapper.addMain(map);
	}
	/**
	 * @param map
	 * 插入从表信息
	 */
	public void addSubsidiaryOne(Map<String, Object>map){
		/*SqlSession session = this.getSqlSession();
		session.insert("com.gljx.web.dao.ImporExcelMapper.addSubsidiaryOne",map);*/
		ImporExcelMapper.addSubsidiaryOne(map);
	}/**
	 * @param map
	 * 插入从表信息
	 */
	public void addSubsidiaryTwo(Map<String, Object>map){
		/*SqlSession session = this.getSqlSession();
		session.insert("com.gljx.web.dao.ImporExcelMapper.addSubsidiaryTwo",map);*/
		ImporExcelMapper.addSubsidiaryTwo(map);
	
	}/**
	 * @param map
	 * 插入从表信息
	 */
	public void addSubsidiaryThr(Map<String, Object>map){		
		ImporExcelMapper.addSubsidiaryThr(map);
	}
	
	
	/**
	 * @param danju
	 * @return 根据单据号返回主表ID 用于子表插入
	 */			  
	public String findBydanjuOne(String danju){	
		return ImporExcelMapper.findBydanjuOne(danju);
	}
	/**
	 * @param danju
	 * @return 根据单据号返回主表ID 用于子表插入
	 */			  
	public String findBydanjuTwo(String danju){
		/*SqlSession session = this.getSqlSession();
		String fid=session.selectOne("com.gljx.web.dao.ImporExcelMapper.findBydanjuTwo", danju);*/
		return ImporExcelMapper.findBydanjuTwo(danju);
	}/**
	 * @param danju
	 * @return 根据单据号返回主表ID 用于子表插入
	 */			  
	public String findBydanjuThr(String danju){
		/*SqlSession session = this.getSqlSession();
		String fid=session.selectOne("com.gljx.web.dao.ImporExcelMapper.findBydanjuThr", danju);*/
		//session.close();
		return ImporExcelMapper.findBydanjuThr(danju);
	}
	/**
	 * @param mp
	 * @return  根据身份证和准假车型 获取唯一的学员信息  用于插入子表结果表
	 */								
	public List<Map<String, String>> findByCerno(Map<String, String> mp){
		/*SqlSession session = this.getSqlSession();
		List<Map<String, String>> map = session.selectList("com.gljx.web.dao.ImporExcelMapper.findByCerno",mp);*/
		//session.close();
		return ImporExcelMapper.findByCerno(mp);
	}
	
	/**
	 * @param mp
	 * @return  根据身份证和准假车型 获取唯一的学员信息  用于插入子表结果表
	 */								
	public List<Map<String, String>> findByCernoTwo(Map<String, String> mp){
		/*SqlSession session = this.getSqlSession();
		List<Map<String, String>> map = session.selectList("com.gljx.web.dao.ImporExcelMapper.findByCernoTwo",mp);
		//session.close();
*/		return ImporExcelMapper.findByCernoTwo(mp);
	}
	
	/**
	 * @param mp
	 * @return  根据身份证和准假车型 获取唯一的学员信息  用于插入子表结果表
	 */								
	public List<Map<String, String>> findByCernoThree(Map<String, String> mp){
		/*SqlSession session = this.getSqlSession();
		List<Map<String, String>> map = session.selectList("com.gljx.web.dao.ImporExcelMapper.findByCernoThree",mp);*/
		//session.close();
		return ImporExcelMapper.findByCernoThree(mp);
	}
	
	/* 学生状态(1: 已报名，2: 科目一通过，3: 科目一未通过，4: 科目二通过,科目三未开始，5: 科目二未通过,科目三未开始，6:科目二练车中,科目三未开始，
	 * 7:科目三通过,科目二通过，8:科目三未通过,科目二通过，9:科目三练车中,科目二通过，10:已结业，11:已退学 ，12:模拟考试合格，
	13:已过期，14:作废，15:科目三练车中,科目二未通过，16:科目三练车中,科目二未开始，17:科目三通过,科目二未通过，18:科目三通过,科目二练车中，19:科目三通过,科目二未开始，
	20:科目二未通过,科目三练车中)*/
	public String zhuangTai(Map<String, String> map){
		String km = map.get("km");
		String score = map.get("score");
		String zhuangTai = "";
		if(km.equals("科目一")){
			if(score.equals("90")){
				zhuangTai="2";
			}else{
				zhuangTai="3";
			}
		}else if(km.equals("科目二")){
			if(score.equals("90")){
				zhuangTai="4";
			}else{
				zhuangTai="5";
			}
		}else if(km.indexOf("科目三")>=0){
			if(score.equals("90")){
				zhuangTai="7";
			}else{
				zhuangTai="8";
			}
		}
		return zhuangTai;
	}
	/**
	 * @param zhuangtai
	 * @param km
	 * @param score
	 * @param time
	 * 获取sql
	 * @return
	 */
	public String zhuangTaiTime(String zhuangtai,String km,String score,String time,String cerno,String type){
		StringBuffer sql = new StringBuffer("update CT_DS_STUDENT set ");
		sql.append(" CFSTUDENTSTATE = '"+zhuangtai+"'  ,  ");
		if(km.equals("科目一")){
			if(score.equals("90")){
				sql.append(" cfkmyherq  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("60")){//,jdbcType=VARCHAR},
				sql.append(" cfkmybjgsj  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("0")){
				sql.append(" cfkmyksqksj  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}
		}else if(km.equals("科目二")){
			if(score.equals("90")){
				sql.append(" cfkmehgrq  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("60")){//,jdbcType=VARCHAR},
				sql.append(" cfkmebjgsj  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("0")){
				sql.append(" cfkmeksqksj  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}
		}else if(km.indexOf("科目三")>=0){
			if(score.equals("90")){
				sql.append(" cfkmshgrq  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("60")){//,jdbcType=VARCHAR},
				sql.append(" cfkmsbjgsj  = "+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}else if(score.equals("0")){
				sql.append(" cfkmsksqksj  ="+"(to_timestamp('"+time+"','yyyy-mm--dd hh24:mi:ss.ff'))");
			}
		}
		//System.out.println("(to_timestamp("+time+",'yyyy-mm--dd hh24:mi:ss.ff'))");
		//(to_timestamp(time,'yyyy-mm--dd hh24:mi:ss.ff'))
		sql.append(" where cfidcardno = '"+cerno+"' and cfslzjcxdh = '"+type+"'");
		return sql.toString();
	}
	/**
	 * @param sql
	 * 修改状态
	 */
	public void updateStuXinXi(String sql){
		SqlSession session = this.getSqlSession();
		session.update("com.gljx.web.dao.ImporExcelMapper.updateStuXinXi",sql);
		//session.commit();
		//session.close();
	}
	public List<Map<String, String>> stuShow(String sql){
		SqlSession session = this.getSqlSession();
		List<Map<String, String>> stuMap = session.selectList("com.gljx.web.dao.ImporExcelMapper.stuShow",sql);
		//session.close();
		return stuMap;
	}

}
